package com.itheima.service;

//import jxl.Workbook;
//import org.apache.poi.ss.usermodel.Workbook;

import cn.afterturn.easypoi.csv.CsvExportUtil;
import cn.afterturn.easypoi.csv.entity.CsvExportParams;
import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.itheima.mapper.ResourceMapper;
import com.itheima.mapper.UserMapper;
import com.itheima.pojo.Resource;
import com.itheima.pojo.User;
import com.itheima.utils.EntityUtils;
import com.itheima.utils.ExcelExportEngine;
import com.opencsv.CSVWriter;
import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import net.sf.jasperreports.engine.JREmptyDataSource;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.data.JRBeanArrayDataSource;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import tk.mybatis.mapper.entity.Example;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
@Slf4j
public class UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private ResourceMapper resourceMapper;

    public List<User> findAll() {
        return userMapper.selectAll();
    }

    public List<User> findPage(Integer page, Integer pageSize) {
        PageHelper.startPage(page, pageSize);  //开启分页
        Page<User> userPage = (Page<User>) userMapper.selectAll(); //实现查询
        return userPage.getResult();
    }

    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-ss");

    /**
     * @param response
     * @return void
     * @description jxl导出excel文件
     * @author 31115
     * @date 2022/12/11 1:07
     */
   /* public void downLoadByJxl(HttpServletResponse response) {

        try {
            //创建工作簿
            ServletOutputStream outputStream = response.getOutputStream();
            WritableWorkbook workbook = Workbook.createWorkbook(outputStream);

            //创建工作表
            WritableSheet sheet = workbook.createSheet("一个Jxl框架的导出", 1);

            //设置列宽
            sheet.setColumnView(0, 5); //第一列 依次往下
            sheet.setColumnView(1, 8);
            sheet.setColumnView(2, 15);
            sheet.setColumnView(3, 15);
            sheet.setColumnView(4, 30);

            //处理标题
            //定义标题：
            String[] title = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};

            //遍历设置单元格数据
            Label label = null; //定义单元格参数对象
            for (int i = 0; i < title.length; i++) {
                label = new Label(i, 0, title[i]); //int c 列角标, int r 行角标, String cont 单元格中数据
                sheet.addCell(label); //添加行列数据 单元格
            }

            //处理导出标题除外的单元格内容
            List<User> userList = this.findAll();
            int rowIndex = 1;
            for (User user : userList) {
                //有多少数据就产生多少行数据
                label = new Label(0, rowIndex, user.getId().toString()); //编号
                sheet.addCell(label);

                label = new Label(1, rowIndex, user.getUserName()); //姓名
                sheet.addCell(label);

                label = new Label(2, rowIndex, user.getPhone()); //手机号
                sheet.addCell(label);

                label = new Label(3, rowIndex, simpleDateFormat.format(user.getHireDate())); //入职日期
                sheet.addCell(label);

                label = new Label(4, rowIndex, user.getAddress()); //现住址
                sheet.addCell(label);

                rowIndex++;  //行每次加一往下迭代
            }

            //导出文件
            //需要一个文件名 两个头
            //文件名称：
            String fileName = "一个Jxl框架的导出.xls";

            // 设置文件的打开方式和mime类型    s + s1 = 文件打开方式      文件名因为是中文，过浏览器的时候会乱码所以改下编码
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));

            response.setContentType("application/vnd.ms-excel"); //文件mime类型

            //导出
            workbook.write();
            //释放资源
            workbook.close();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }*/

    //poi简单导出数据
    public void downLoadXlsx(HttpServletResponse response) throws Exception {
        //设置标题
        //创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("一个带样式的工作表");

        //        设置列宽
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 8 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.setColumnWidth(4, 30 * 256);

        //带边框线、行高、合并单元格、对齐方式、字体

        CellStyle bigTitleRowCellStyle = workbook.createCellStyle(); //大标题列样式设置
        //设置边框线
        bigTitleRowCellStyle.setBorderBottom(BorderStyle.THIN); //下
        bigTitleRowCellStyle.setBorderLeft(BorderStyle.THIN); //左
        bigTitleRowCellStyle.setBorderRight(BorderStyle.THIN); //右
        bigTitleRowCellStyle.setBorderTop(BorderStyle.THIN); //上

        Row bigRow = sheet.createRow(0); //第一行
        bigRow.setHeightInPoints(42);   //设置行高
        for (int i = 0; i < 5; i++) {
            Cell cell = bigRow.createCell(i); //列
            cell.setCellStyle(bigTitleRowCellStyle); //设置列样式
        }

        //合并单元格
        //int firstRow 第一行, int lastRow 最后一行, int firstCol 第一列, int lastCol 最后一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

        //对齐方式
        //居中对齐：
        bigTitleRowCellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        bigTitleRowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中

        sheet.getRow(0).getCell(0).setCellValue("用户信息数据"); //输出字符

        //设置字体
        Font font = workbook.createFont();
        font.setFontName("黑体"); //字体格式
        font.setFontHeightInPoints((short) 18); //字体大小
        bigTitleRowCellStyle.setFont(font); //设置样式


        //小标题列样式设置
        CellStyle smallTitleRowCellStyle = workbook.createCellStyle();
        smallTitleRowCellStyle.cloneStyleFrom(bigTitleRowCellStyle);

        //设置字体
        Font smallFont = workbook.createFont();
        smallFont.setFontName("宋体"); //字体格式
        smallFont.setFontHeightInPoints((short) 12); //字体大小
        smallFont.setBold(true); //加粗
        smallTitleRowCellStyle.setFont(smallFont); //设置样式

        String[] smallTitle = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};

        Row smallRow = sheet.createRow(1);
        smallRow.setHeightInPoints(32);   //设置行高
        for (int i = 0; i < smallTitle.length; i++) {
            Cell cell = smallRow.createCell(i);
            cell.setCellValue(smallTitle[i]);
            cell.setCellStyle(smallTitleRowCellStyle);
        }


        //单元格内容列样式设置
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.cloneStyleFrom(bigTitleRowCellStyle);

        //设置字体
        Font cellStyleFont = workbook.createFont();
        cellStyleFont.setFontName("宋体"); //字体格式
        cellStyleFont.setFontHeightInPoints((short) 11); //字体大小
        cellStyleFont.setBold(false); //加粗
        cellStyle.setFont(cellStyleFont); //设置样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT); //水平居中

        List<User> userList = this.findAll();

        int cellStyleRowCount = 2;
        Row cellStyleRow = null;
        Cell cell = null;
        for (User user : userList) {
            cellStyleRow = sheet.createRow(cellStyleRowCount);
            cell = cellStyleRow.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell.setCellStyle(cellStyle);

            cell = cellStyleRow.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(cellStyle);

            cellStyleRowCount++;
        }

        //设置打开方式和头信息
        String filename = "员工数据.xlsx";
        xlsx(response, workbook, filename);

    }

    //poi简单的导入数据库数据
    public void uploadExcel(MultipartFile file) {

        try {
            //读取excel文件数据
            //读取工作表
            Workbook workbook = new XSSFWorkbook(file.getInputStream());

            //设置读取工作表索引
            Sheet sheetAt = workbook.getSheetAt(0);

            //设置读取内容 获取到最后一行内容的索引
            int lastRowIndex = sheetAt.getLastRowNum();

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            //        以下三个为了节省栈内存，所以提到循环的外面
            User user = null;
            Row row = null;  //一行
            Cell cell = null; //单元格
            for (int i = 1; i <= lastRowIndex; i++) {
                row = sheetAt.getRow(i);
                user = new User();

                user.setUserName(row.getCell(0).getStringCellValue());  //用户名

                try {
                    user.setPhone(row.getCell(1).getStringCellValue()); //手机号
                } catch (Exception e) {
                    user.setPhone(row.getCell(1).getNumericCellValue() + ""); //手机号
                }

                user.setProvince(row.getCell(2).getStringCellValue()); //省份

                user.setCity(row.getCell(3).getStringCellValue()); //城市

                user.setSalary(((Double) row.getCell(4).getNumericCellValue()).intValue());  //工资

                user.setHireDate(sdf.parse(row.getCell(5).getStringCellValue())); //入职时间

                user.setBirthday(sdf.parse(row.getCell(6).getStringCellValue())); //生日

                user.setAddress(row.getCell(7).getStringCellValue());   //现住址

                userMapper.insert(user);

            }

            workbook.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //使用模板导出excel
    public void downLoadTemplateXlsx(HttpServletResponse response) throws Exception {
        //获取模板根目录
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/excel_template/userList.xlsx");

        List<User> users = userMapper.selectAll();

        //使用模板
        Workbook workbook = new XSSFWorkbook(template);

        Sheet sheet = workbook.getSheetAt(0);

        //取工作表2模板样式 塞到第一个工作表
        CellStyle cellStyle = null;
        try {
            cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
        } catch (Exception e) {
            cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
        }

        //导出数据
        Row row = null;
        int count = 2;
        Cell cell = null;
        for (User user : users) {
            row = sheet.createRow(count);
            row.setHeightInPoints(15);
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(cellStyle);

            cell = row.createCell(3);
            cell.setCellValue(simpleDateFormat.format(user.getHireDate()));
            cell.setCellStyle(cellStyle);

            cell = row.createCell(4);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(cellStyle);
            count++;
        }

        //删除工作表2
        try {
            workbook.removeSheetAt(1);
        } catch (Exception e) {
            log.info("无其他工资表{}", e);
        }
        //设置打开方式和头信息
        String filename = "员工数据.xlsx";
        xlsx(response, workbook, filename);

    }

    //下载某个员工的基本信息
    public void downloadByTemplateXlsx(Long id, HttpServletResponse response) throws Exception {
        //先获取模板
        //获取模板根目录
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/excel_template/userInfo.xlsx");

        //查询员工信息
        User user = userMapper.selectByPrimaryKey(id);

        //封装数据到模板中
//        用户名 第2行第2列
//        手机号 第3行第2列
//        生日第4行第2列
//        工资 第5行第2列
//        入职日期 第6行第2列
//        省份 第7行第2列
//        现住址 第8行第2列
//        司龄 第6行第4列
//        城市 第7行第4列
//        照片的位置第2行到第5行第3列到第4列
        Workbook workbook = new XSSFWorkbook(template);

        Sheet sheetAt = workbook.getSheetAt(0);

        int count = 1; //2
        Row row = null;
        Cell cell = null;

        row = sheetAt.getRow(count);
        cell = row.getCell(1);
        cell.setCellValue(user.getUserName()); //用户名

        count++; //3

        row = sheetAt.getRow(count);
        cell = row.getCell(1);
        cell.setCellValue(user.getPhone()); //手机号

        count++; //4

        row = sheetAt.getRow(count);
        cell = row.getCell(1);
        cell.setCellValue(simpleDateFormat.format(user.getBirthday())); //生日

        count++; //5

        row = sheetAt.getRow(count);
        cell = row.getCell(1);
        cell.setCellValue(user.getSalary()); //工资

        count++; //6

        row = sheetAt.getRow(count);
        cell = row.getCell(1);
        cell.setCellValue(simpleDateFormat.format(user.getHireDate())); //入职日期

        cell = row.getCell(3);
        cell.setCellFormula("CONCATENATE(DATEDIF(B6,TODAY(),\"Y\"),\"年\",DATEDIF(B6,TODAY(),\"YM\"),\"个月\")");  //入公司多少年

        count++; //7

        row = sheetAt.getRow(count);
        cell = row.getCell(1);
        cell.setCellValue(user.getProvince()); //省份

        cell = row.getCell(3);
        cell.setCellValue(user.getCity());  //城市

        count++; //8

        row = sheetAt.getRow(count);
        cell = row.getCell(1);
        cell.setCellValue(user.getAddress()); //现住址

        //照片位置
        //先创建一个字节输出流
        ByteOutputStream outputStream = new ByteOutputStream();
        // 读取图片 放入一个带有缓存区的图片类中 ImageIO类             绝对路径
        BufferedImage image = ImageIO.read(new File(classpath + user.getPhoto()));
        //取后缀名   .toUpperCase();转大写
        String extName = user.getPhoto().substring(user.getPhoto().lastIndexOf(".") + 1).toUpperCase();
        //然后把图片写入到字节输出流中
        ImageIO.write(image, extName, outputStream); //写

        //Patriarch 负责创建图片  ClientAnchor负责设置图片大小和位置
        Drawing patriarch = sheetAt.createDrawingPatriarch(); //Patriarch

        //指定图片位置   前四个是设置偏移量   后四个是设置位置   开始列3    开始行2    结束列4    结束行5
        ClientAnchor clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 1, 4, 5); //ClientAnchor

        //开始把图片写入到sheet指定的位置
        switch (extName) {
            case "JPG": {
                patriarch.createPicture(clientAnchor, workbook.addPicture(outputStream.toByteArray(),
                        XSSFWorkbook.PICTURE_TYPE_JPEG));
                break;
            }
            case "PNG": {
                patriarch.createPicture(clientAnchor, workbook.addPicture(outputStream.toByteArray(),
                        XSSFWorkbook.PICTURE_TYPE_PNG));
                break;
            }
            default: {
                break;
            }
        }

        //下载合同
        //设置打开方式和头信息
        String filename = "员工" + user.getUserName() + "的数据.xlsx";
        xlsx(response, workbook, filename);
    }

    private void xlsx(HttpServletResponse response, Workbook workbook, String filename) throws IOException {
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        //创建工作簿
        workbook.write(response.getOutputStream());
        workbook.close();
    }


    //基于工具类动态获取模板数值并封装对应用户数据
    public void downloadByTemplateXlsxUtils(Long id, HttpServletResponse response) throws Exception {
        //先获取模板
        //获取模板根目录
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/excel_template/userInfo2.xlsx");
        //查询员工信息
        User user = userMapper.selectByPrimaryKey(id);

        Workbook workbook = new XSSFWorkbook(template);

        workbook = ExcelExportEngine.writeExcel(user, workbook, classpath + user.getPhoto());

        String filename = "员工" + user.getUserName() + "的数据.xlsx";
        xlsx(response, workbook, filename);
    }

    //百万数据的导出
    //使用sax方式逐行解析 防止堆内存溢出
    //舍弃模板和一些样式
    public void downLoadMillion(HttpServletResponse response) throws Exception {

        //创建sax模式的POI
        Workbook workbook = new SXSSFWorkbook();
//        Workbook workbook = new XSSFWorkbook();
        // 每一个工作表里可以存储1000000条数据
        //每个工作表第一行填写的是 标题
        //分页取出 一次分页取一百万条

        int pageNum = 1; //每页条数
        int num = 0;    //总数据量
        int rowIndex = 1; //每一个工作页的行数
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        while (true) {

            List<User> userList = this.findPage(pageNum, 1000000);
            //判断当取出数据为null时就跳出循环
            if (CollectionUtils.isEmpty(userList)) {
                break;
            }

            //设置标题 编号	姓名	手机号	入职日期	现住址
            if (num % 1000000 == 0) {
                sheet = workbook.createSheet("第" + ((num / 1000000) + 1) + "用户工作表");

                rowIndex = 1;
                String[] title = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
                //        设置列宽
                sheet.setColumnWidth(0, 8 * 256);
                sheet.setColumnWidth(1, 12 * 256);
                sheet.setColumnWidth(2, 15 * 256);
                sheet.setColumnWidth(3, 15 * 256);
                sheet.setColumnWidth(4, 30 * 256);
                Row titleRow = sheet.createRow(0);  //标题头行
                //遍历标题
                for (int i = 0; i < 5; i++) {
                    titleRow.createCell(i).setCellValue(title[i]);
                }
            }

            //设置工作表内容
            for (User user : userList) {
                row = sheet.createRow(rowIndex);
                cell = row.createCell(0);
                cell.setCellValue(user.getId());

                cell = row.createCell(1);
                cell.setCellValue(user.getUserName());

                cell = row.createCell(2);
                cell.setCellValue(user.getPhone());

                cell = row.createCell(3);
                cell.setCellValue(simpleDateFormat.format(user.getHireDate()));

                cell = row.createCell(4);
                cell.setCellValue(user.getAddress());

                rowIndex++;
                num++;
            }
            pageNum++;
        }
        String filename = "百万员工的数据.xlsx";
        xlsx(response, workbook, filename);
    }

    //csv文件导出
    public void downLoadCSV(HttpServletResponse response) throws Exception {
        //创建流
        ServletOutputStream outputStream = response.getOutputStream();
        //创建csv对象
        CSVWriter writer = new CSVWriter(new OutputStreamWriter(outputStream, "UTF-8"));
        //设置头信息
        String filename = "百万员工的数据.csv";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("text/csv");

        //写数据
        //创建头信息
        String[] title = new String[]{"编号", "姓名", "手机号", "入职日期", "现住址"};
        writer.writeNext(title);

        int count = 1;
        while (true) {
            //        创建用户信息
            List<User> userList = this.findPage(count, 1000000);
            if (CollectionUtils.isEmpty(userList)) {
                break;
            }

            for (User user : userList) {
                //封装信息
                writer.writeNext(new String[]{user.getId().toString(), user.getUserName(), user.getPhone(),
                        simpleDateFormat.format(user.getHireDate()), user.getAddress()});
            }
            count++;
            writer.flush();
        }
        writer.close();
    }

    public User findById(Long id) {

        User user = userMapper.selectByPrimaryKey(id);

        Resource resource = new Resource();
        resource.setUserId(id);

        List<Resource> resources = resourceMapper.select(resource);

        user.setResourceList(resources);


        return user;
    }

    //word
    public void downloadContract(Long id, HttpServletResponse response) throws Exception {
        //1.读取到模板
        //获取模板根目录
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/word_template/contract_template.docx");
        XWPFDocument word = new XWPFDocument(new FileInputStream(template));

        //查询当前用户User---》map
        User user = this.findById(id);
        Map<String, String> params = new HashMap<>();
        params.put("userName", user.getUserName());
        params.put("hireDate", simpleDateFormat.format(user.getHireDate()));
        params.put("address", user.getAddress());

        //替换数据
        //处理正文开始
        List<XWPFParagraph> paragraphs = word.getParagraphs(); //得到段落
        for (XWPFParagraph paragraph : paragraphs) {
            List<XWPFRun> runs = paragraph.getRuns(); //得到片段
            for (XWPFRun run : runs) {
                String text = run.getText(0); //每一个小片段的文本
                for (String key : params.keySet()) {
                    if (text.contains(key)) {     //拿着文本和map里的key做比较  有没有包含map里的key
                        //如果包含了就重新往里面放值     第0个位置替换
                        run.setText(text.replaceAll(key, params.get(key)), 0);
                    }
                }
            }
        }

//      处理表格开始     名称	价值	是否需要归还	照片

        List<Resource> resourceList = user.getResourceList();  //表格中需要的数据

        XWPFTable xwpfTable = word.getTables().get(0); //获取表格  现在模板里面只有一个表格

        XWPFTableRow row = xwpfTable.getRow(0);
        int rowIndex = 1;
        //遍历数据，有多少数据添加多少表格行
        for (Resource resource : resourceList) {

            //添加行  深拷贝 （参数：拷贝那个表格，拷贝哪一行的数据，数据拷贝到第几行）
            copyRow(xwpfTable, row, rowIndex);
            XWPFTableRow row1 = xwpfTable.getRow(rowIndex);  //得到第二行
            //插入数据
            row1.getCell(0).setText(resource.getName());
            row1.getCell(1).setText(resource.getPrice().toString());
            row1.getCell(2).setText(resource.getNeedReturn() ? "需求" : "不需要");

            //插入图片                      觉对路径
            File imageFile = new File(classpath, "/static" + resource.getPhoto());
            setCellImage(row1.getCell(3), imageFile);
            rowIndex++;
        }
        //     处理表格开始结束
        //        4、导出word
        String filename = "员工(" + user.getUserName() + ")合同.docx";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        word.write(response.getOutputStream());
    }

    //向单元格里面写入图片
    private void setCellImage(XWPFTableCell cell, File imageFile) {

        //获取段落，获取到段落以后获取到段落里的片段，向片段里面放数据
        //第一个段落，createRun 产生一个片段
        XWPFRun run = cell.getParagraphs().get(0).createRun();

        //输入流
        try (FileInputStream inputStream = new FileInputStream(imageFile);) {

            //设置图片
            //InputStream pictureData, int pictureType, String filename, int width, int height
            //  输入流                     图片类型            图片名词        宽度          高度
            run.addPicture(inputStream, XWPFDocument.PICTURE_TYPE_JPEG, imageFile.getName(), Units.toEMU(100), Units.toEMU(50));  //Units.toEMU()=public static final int EMU_PER_POINT = 12700;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //深拷贝
    private void copyRow(XWPFTable xwpfTable, XWPFTableRow sourceRow, int rowIndex) {

        XWPFTableRow targetRow = xwpfTable.insertNewTableRow(rowIndex);  //插入新的row

        //getCtRow 获取行属性      setTrPr 复制行的属性
        targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr()); //往新行里面设置属性

        //获取原行的单元格
        List<XWPFTableCell> cells = sourceRow.getTableCells();
        if (CollectionUtils.isEmpty(cells)) { //如果为空就不处理了
            return;
        }

        XWPFTableCell targetCell = null;
        for (XWPFTableCell cell : cells) {  //遍历原单元格数据，插入到新行的单元格
            targetCell = targetRow.addNewTableCell();  //获取新的单元格
            //附上单元格样式
            //单元格属性
            // getCTTc 得到每一个单元格   setTcPr 设置每个单元格的属性
            targetCell.getCTTc().setTcPr(cell.getCTTc().getTcPr());

            //设置段落属性
            // getParagraphs().get(0).getCTP().setPPr 设置第一个段落的属性    你，好
            targetCell.getParagraphs().get(0).getCTP().setPPr(cell.getParagraphs().get(0).getCTP().getPPr());
        }

    }

    //EayPoi 导出
    public void downLoadXlsxWithEayPoi(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //查询用户数据
        List<User> users = userMapper.selectAll();

        //指定导出的格式是高版本的格式
        ExportParams exportParams = new ExportParams("员工信息", "数据", ExcelType.XSSF);
        //直接使用easyPOI提供的方法
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, users);

        String filename = "员工信息.xlsx";
        //            设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);

    }

    //使用EasyPoi导入数据
    public void uploadExcelWithException(MultipartFile file) throws Exception {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(1); //有多少行标题
        importParams.setHeadRows(1); //有多少行头

        //创建工具类设置参数  返回数据
        List<User> userList = ExcelImportUtil.importExcel(file.getInputStream(), User.class, importParams);

        for (User user : userList) {
            user.setId(null);
            userMapper.insert(user);
        }
    }


    //使用easyPoi以模板的形式导出某个用户
    public void downloadByTemplateEasyPoiUtils(Long id, HttpServletResponse response) throws Exception {

        //1.读取到模板
        //获取模板根目录
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/excel_template/userInfo3.xlsx");

        //读取模板文件  String templateUrl, boolean scanAllsheet (遍历sheet工作表), String... sheetName
        TemplateExportParams exportParams = new TemplateExportParams(template.getPath(), true);

        //还是转成map
        User user = userMapper.selectByPrimaryKey(id);
        Map<String, Object> userMap = EntityUtils.entityToMap(user);

        //处理图片
        ImageEntity imageEntity = new ImageEntity();
        imageEntity.setColspan(1);
        imageEntity.setRowspan(4);
        imageEntity.setUrl(user.getPhoto());
        userMap.put("photo", imageEntity);

        //封装参数
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, userMap);
        String filename = user.getUserName() + "员工信息.xlsx";
        //上传导出
        xlsx(response, workbook, filename);

    }

//    CSVEasyPOI 导出
    public void downLoadCSVEasyPOI(HttpServletResponse response) throws Exception {
        //创建流
        ServletOutputStream outputStream = response.getOutputStream();
        //设置头信息
        String filename = "员工的数据.csv";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("text/csv");
//            创建一个用来写入到csv文件中的writer
        CsvExportParams csvExportParams = new CsvExportParams();
        //设置忽略的列
        csvExportParams.setExclusions(new String[]{"照片"}); //这里写表头 中文

        List<User> userList = userMapper.selectAll();
        //CsvExportParams params, Class<?> pojoClass, Collection<?> dataSet, OutputStream outputStream
        CsvExportUtil.exportCsv(csvExportParams, User.class, userList, outputStream);

    }

//    EasyPOIWord 导出
    public void downloadContractEasyPOIWord(Long id, HttpServletResponse response) throws Exception {
        //现获取模板
        //1.读取到模板
        //获取模板根目录
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/word_template/contract_template2.docx");


        //创建实体类获取参数
        User user = this.findById(id);
        //将user转为map
        Map<String, Object> params = new HashMap<>();
        params.put("userName", user.getUserName());
        params.put("hireDate", simpleDateFormat.format(user.getHireDate()));
        params.put("address", user.getAddress());
        ImageEntity imageEntity1 = new ImageEntity();
        imageEntity1.setUrl(classpath.getPath() + user.getPhoto());
        imageEntity1.setHeight(100);
        imageEntity1.setWidth(50);
        params.put("myImage", imageEntity1);

        //将表格中的内容进行转换
        List<Map> respMapList = new ArrayList<>();
        Map<String, Object> map = null;
        for (Resource resource : user.getResourceList()) {
            map = new HashMap<>();
            map.put("name", resource.getName());
            map.put("price", resource.getPrice());
            map.put("needReturn", resource.getNeedReturn());
            ImageEntity imageEntity = new ImageEntity();
            imageEntity.setUrl(classpath.getPath() + "/static/" + resource.getPhoto());
            imageEntity.setHeight(100);
            imageEntity.setWidth(50);
            map.put("photo", imageEntity);

            respMapList.add(map);
        }
        params.put("maplist", respMapList);

        XWPFDocument word = WordExportUtil.exportWord07(template.getPath(), params);

        //        4、导出word
        String filename = "员工(" + user.getUserName() + ")合同.docx";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        word.write(response.getOutputStream());


    }

    //springboot自带的链接 数据源
    @Autowired
    private HikariDataSource hikariDataSource;

    //JasperPrint 模板 导出pdf
    public void downLoadPDFByDB(HttpServletResponse response) throws Exception {
        //获取模板
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/pdf_template/UserInfo.jasper");

        Map map = new HashMap();
        //解析数据
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(template), map, hikariDataSource.getConnection());
        //设置浏览器参数 解析文件 一个流两个头
        String filename = "用户信息.pdf";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
        //上传
        JasperExportManager.exportReportToPdfStream(jasperPrint, response.getOutputStream());

    }

    //链接jdbc
    private Connection getCon() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/report_manager_db", "root", "root");
        return connection;
    }

    //PDFStr 模板自定义变量 处理时间
    public void downLoadPDFStr(HttpServletResponse response) throws Exception {
        //获取模板
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/pdf_template/userList2.jasper");
        Map map = new HashMap();

        Example example = new Example(User.class);
        example.setOrderByClause("province"); //根据省份排序
        List<User> userList = userMapper.selectByExample(example); //排好序了取查数据库

        //处理时间格式
        userList = userList.stream()
                .map(user -> {
                    user.setHireDateStr(simpleDateFormat.format(user.getHireDate()));
                    return user;
                }).collect(Collectors.toList());

        //解析数据
        JRBeanCollectionDataSource source = new JRBeanCollectionDataSource(userList); //数据源
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(template), map, source);
        //设置浏览器参数 解析文件 一个流两个头
        String filename = "用户信息.pdf";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
        //上传
        JasperExportManager.exportReportToPdfStream(jasperPrint, response.getOutputStream());

    }


    //表格形式pdf
    public void downloadByTemplatePDF(Long id, HttpServletResponse response)throws Exception {
        //获取模板
        File classpath = new File(ResourceUtils.getURL("classpath:").getPath());
        //excel模板
        File template = new File(classpath, "/pdf_template/userInfo2.jasper");

        User user = userMapper.selectByPrimaryKey(id);
        Map<String, Object> params = EntityUtils.entityToMap(user);

        //解析数据
        JasperPrint jasperPrint = JasperFillManager.fillReport(new FileInputStream(template), params,new JREmptyDataSource());
        //设置浏览器参数 解析文件 一个流两个头
        String filename = "用户信息.pdf";
        response.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/pdf");
        //上传
        JasperExportManager.exportReportToPdfStream(jasperPrint, response.getOutputStream());

    }
}
